USE [aircond]
GO

/****** Object:  Table [dbo].[SData_AUD]    Script Date: 10/25/2012 18:24:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SData_AUD](
	[AUDIT_ID] [numeric](19, 0) IDENTITY(1,1) NOT NULL,
	[IDX] [smallint] NULL,
	[NID] [smallint] NULL,
	[DATA] [char](64) NULL,
	[TIMING] [char](64) NULL,
	[EVENTS] [char](64) NULL,
	[TOLL] [char](64) NULL,
	[LINK] [tinyint] NULL,
	[RCMD] [char](1) NULL,
	[MARK] [char](16) NULL,
	[NAME] [char](32) NULL,
	[DEFAULTTEMP] [decimal](8, 2);NULL,
	[ROOMTEMP] [decimal](8, 2); NULL,
	[MINTEMP] [decimal](8, 2); NULL,
	[UPDATEDATE] [datetime] NULL
)ON [PRIMARY]

GO

ALTER TABLE [SData_AUD] ADD [DEFAULTTEMP] decimal(8, 2);
ALTER TABLE [SData_AUD] ADD [ROOMTEMP] decimal(8, 2);
ALTER TABLE [SData_AUD] ADD [MINTEMP] decimal(8, 2);
GO

ALTER TABLE SData_AUD ALTER COLUMN DEFAULTTEMP decimal(8, 2);
ALTER TABLE SData_AUD ALTER COLUMN ROOMTEMP decimal(8, 2);
ALTER TABLE SData_AUD ALTER COLUMN MINTEMP decimal(8, 2);
GO

CREATE INDEX IDX_SDATA_AUD_1
ON SData_AUD (nid,updateDate);
GO

CREATE TRIGGER SDATA_TRIGGER ON SDATA
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON
INSERT INTO SDATA_AUD
SELECT 
IDX, NID, DATA, TIMING, EVENTS,
TOLL, LINK, RCMD, MARK, NAME,
CASE CHARINDEX('D',data)
	WHEN 0 then null
	ELSE CAST(SUBSTRING(DATA, CHARINDEX('D',data)+1, 2) AS INT)
END DEFAULTTEMP,
CASE CHARINDEX('C',data)
	WHEN 0 then null
	ELSE CAST(SUBSTRING(DATA, CHARINDEX('C',data)+1, 2) AS INT)
END ROOMTEMP,
CASE CHARINDEX('X',data)
	WHEN 0 then null
	ELSE CAST(SUBSTRING(DATA, CHARINDEX('X',data)+1, 2) AS INT)
END MINTEMP,
GETDATE()
FROM INSERTED
GO

